package mysqlCnnection;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * This class update tables: campaigns, activedCampaign
 * all update of this tables in this class
 * use to create campaign and active campaign  
 * @author matan
 * @author sharon
 * @author ido
 * @author michael 
 */
public class CampaignUpdate extends ConnectionTOmysql 
{
	private Connection connection;
	
	/**
	 * This is the default constructor
	 * @throws IlegalMySQLPasswordORUserNameException 
	 */
	public CampaignUpdate() throws IlegalMySQLPasswordORUserNameException
	{
		super();
		this.connection = super.getConnection();
	}
	
	
	/**
	 * This method return all clients market sectors as String[] 
	 * @return String[] 
	 */
	public String[] loadAllClientsMarketSectors()
	{
		Statement stmt;
		String[] marketsectorsID = null;
		int len=1, i=0;
		Vector<Integer> vecMarketsectorsID = new Vector<Integer>();
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sNumber FROM marketsectors;");
			while(rs.next())
			{
				vecMarketsectorsID.add(rs.getInt(1));
			}	
			len = vecMarketsectorsID.size();
			marketsectorsID = new String[len];
			for (i=0; i < len; i++)
				marketsectorsID[i] = String.valueOf(vecMarketsectorsID.get(i));			
			rs.close();
			return marketsectorsID;
		} catch (SQLException e)
		{
			return null;
		}
		catch (Exception exc)
		{
			return null;
		}		
	}
	
	/**
	 * This method insert campaign to data base
	 * @param cID the campaign id to insert
	 * @param name campaign name
	 * @param sector the clients sector of the campaign
	 * @param message the message to customer
	 * @param pID the id of the product of the campaign
	 * @return  "OK" on success , "IlegalID" on SQL error
	 */
	public String createCampaign(long cID,String name,String sector,
			String message,	long pID )
	{
		try {
			PreparedStatement ps = connection.prepareStatement("INSERT INTO campaigns VALUES (?,?,?,?,?);");
			ps.setLong(1, cID);
			ps.setString(2, name);
			ps.setString(3, message);
			ps.setLong(4, Long.parseLong(sector));		
			ps.setLong(5, pID);		
			ps.executeUpdate();
			ps.close();
			return "OK";
		} catch (SQLException e)
		{
		 	return "IlegalID";
		}
		catch (Exception e)
		{
		 	return null;
		}
	}
	
	/**
	 * This method load from data base all campaignID
	 * @return array of String of all CampaignsID
	 */
	public long[] loadAllCampaigns()
	{
		Statement stmt;
		long[] campaignsID = null;
		int len=1, i=0;
		Vector<Long> vecampaignsID = new Vector<Long>();
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT campaignID FROM campaigns;");
			while(rs.next())
			{
				vecampaignsID.add(rs.getLong(1));
			}	
			len = vecampaignsID.size();
			campaignsID = new long[len];
			for (i=0; i < len; i++)
				campaignsID[i] = vecampaignsID.get(i);			
			rs.close();
			return campaignsID;
		} catch (SQLException e)
		{
			return null;
		}
		catch (Exception exc)
		{
			return null;
		}		
	}
	
	/**
	 * This method load from data base all campaign details
	 * @param campaignID the id of the campaign to load
	 * @return array of string of campaign details
	 */
	public String[] loadCampaign(long campaignID)
	{
		Statement stmt;
		String[] campaignTostr = null;
		long pID = 0;
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM campaigns WHERE campaignID = "+campaignID+";");
			campaignTostr = new String[8];
			if (rs.next())
			{	
				campaignTostr[0] = String.valueOf(rs.getLong(1));//campaignID
				campaignTostr[1] = rs.getString(2);//campaignName
				campaignTostr[2] = rs.getString(3);//message
				campaignTostr[3] = String.valueOf(rs.getInt(4));//sectorNumber
				pID = rs.getLong(5);//productNumber
				rs.close();	
				ResultSet Result = stmt.executeQuery("SELECT * FROM catalogue WHERE cataloguenumber = "+pID+";");
				if (Result.next())
				{
					campaignTostr[4] = Result.getString(1);//price
					campaignTostr[5] = Result.getString(2);//name
					campaignTostr[6] = Result.getString(3);//description
					campaignTostr[7] = Result.getString(4);//cataloguenumber
					return campaignTostr;
				}									
			}
			return null;			
		} catch (SQLException e)
		{
			return null;
		}
	}
	
	/**
	 * This method update campaign details in data base
	 * @param cID the id of the campaign to update
	 * @param name the name of the campaign
	 * @param sector the clients sector of the campaign
	 * @param message the message to customer
	 * @param pID the id of the product of the campaign
	 * @return "OK" on success , "ERROR" on SQL error
	 */
	public String updateCampaign(long cID,String name,String sector,
			String message,	long pID )
	{
		String statementstr = null;
		Statement stmt;
		long tmpsector = Long.parseLong(sector);
		statementstr = String.format("UPDATE campaigns SET campaignName='%s', " +
				"message='%s',sectorNumber=%d, productNumber=%d WHERE campaignID=%d;",
				name,message,tmpsector,pID,cID);
		try 
		{
			stmt = connection.createStatement();
			stmt.executeUpdate(statementstr);
			stmt.close();
			return "OK";
		} 
		catch (SQLException e)
		{
			return "ERROR";
		}	
	}
	
	/**
	 * This method update activeCampaignID details in data base
	 * @param activeCampaignID the id of activeCampaign to update
	 * @param startdate the day that the campaign start run
	 * @param endate the day that the campaign ends 
	 * @param status the status of the campaign
	 * @return "OK" on success , "ERROR" on SQL error
	 */
	public String updateActiveCampaign(long activeCampaignID, Date startdate, Date endate, String status)
	{
		try 
		{
			PreparedStatement ps = connection.prepareStatement("UPDATE activedcampaign SET startdate=?, enddate=?, status=? WHERE ActiveID=?;");
			ps.setDate(1, startdate);
			ps.setDate(2, endate);
			ps.setString(3, status);
			ps.setLong(4, activeCampaignID);			
			ps.executeUpdate();
			ps.close();
			return "OK";
		} 
		catch (SQLException e)
		{
			return "ERROR";
		}	
	}
	
	/**
	 * This method insert activeCampaignID details to data base
	 * @param activeCampaignID the id of activeCampaign to insert
	 * @param startdate the day that the campaign start run
	 * @param endate the day that the campaign ends 
	 * @param status the status of the campaign
	 * @param campaignID the id of the campaign that been active
	 * @return "OK" on success , "ERROR" on SQL error
	 */
	public String saveAtiveCampaign(long activeCampaignID, Date startdate, Date endate, String status , long campaignID)
	{
		try 
		{
			PreparedStatement ps = connection.prepareStatement("INSERT INTO activedcampaign VALUES (?,?,?,?,?);");
			ps.setLong(1, activeCampaignID);	
			ps.setLong(2, campaignID);	
			ps.setDate(3, startdate);
			ps.setDate(4, endate);
			ps.setString(5, status);				
			ps.executeUpdate();
			ps.close();
			return "OK";
		} 
		catch (SQLException e)
		{
			return "ERROR";
		}	
	}
	
	/**
	 * This method load activeCampaignID details from data base as Object[]
	 * @param activeCampaignID the id of activeCampaign to load
	 * @return Object[] or null
	 */
	public Object[] loadActiveCampaign(long activeCampaignID)
	{
		Statement stmt;
		Object[] activeCampaignTostr = null;
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM activedcampaign WHERE ActiveID = "+activeCampaignID+";");
			activeCampaignTostr = new Object[5];
			if (rs.next())
			{	
				activeCampaignTostr[0] = rs.getLong(1);//activeID
				activeCampaignTostr[4] = rs.getLong(2);//campaignID
				activeCampaignTostr[1] = rs.getDate(3);//start date
				activeCampaignTostr[2] = rs.getDate(4);//end date
				activeCampaignTostr[3] = rs.getString(5);//message	
				rs.close();
				return activeCampaignTostr;
			}
			return null;			
		} catch (SQLException e)
		{
			return null;
		}		
	}
	
	/**
	 * This method load from data base all activecampaignID as Vector<Long>
	 * @return Vector<Long>
	 */
	public long[] loadAllactiveCampaigns()
	{
		Statement stmt;
		long[] campaignsID = null;
		int len=1, i=0;
		Vector<Long> vecampaignsID = new Vector<Long>();
		try
		{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT ActiveID FROM activedcampaign;");
			while(rs.next())
			{
				vecampaignsID.add(rs.getLong(1));
			}	
			len = vecampaignsID.size();
			campaignsID = new long[len];
			for (i=0; i < len; i++)
				campaignsID[i] = vecampaignsID.get(i);			
			rs.close();
			return campaignsID;
		} catch (SQLException e)
		{
			return null;
		}
		catch (Exception exc)
		{
			return null;
		}		
	}
	
}
	
	
	
	
	
	
	
	
	

